package com.we.risk.attribution.correct;

public class SqlTemplate {
    // test table user_label_0214
    // online table modify exp_attribution.user_label230228 clk_attribution.user_label230228
    public static final String clkSqlTmplateAsc =
            "select \n"
                    + "a.device_id,\n"
                    + "a.unique_channel,\n"
                    + "a.hfq_click_id,\n"
                    + "b.hfq_origin\n"
                    + "from \n"
                    + "(\n"
                    + "    select \n"
                    + "    device_id,\n"
                    + "    unique_channel,\n"
                    + "    hfq_advertiser_id,\n"
                    + "    hfq_campaign_id,\n"
                    + "    hfq_aid,\n"
                    + "    hfq_click_id,\n"
                    + "    row_number() over(partition by device_id, unique_channel order by dt_time asc) as rn\n"
                    + "    from\n"
                    + "    (\n"
                    + "        SELECT \n"
                    + "        device_id,\n"
                    + "        dt_time,\n"
                    + "        unique_channel,\n"
                    + "        rt_type,\n"
                    + "        max(if(label_name ='hfq_advertiser_id', label_value, null)) as hfq_advertiser_id,\n"
                    + "        max(if(label_name ='hfq_campaign_id', label_value, null)) as hfq_campaign_id,\n"
                    + "        max(if(label_name ='hfq_aid', label_value, null)) as hfq_aid,\n"
                    + "        max(if(label_name ='hfq_click_id', label_value, null)) as hfq_click_id\n"
                    + "        FROM clk_attribution.user_label_0214\n"
                    + "        where device_id = ?\n"
                    + "        and datediff(current_timestamp(), dt_time) <= ?\n"
                    + "        and rt_type = ?\n"
                    + "        group by device_id,dt_time, unique_channel, rt_type\n"
                    + "    ) tmp\n"
                    + ") a \n"
                    + "left join (\n"
                    + "    select \n"
                    + "    channel,\n"
                    + "    hfq_advertiser_id,\n"
                    + "    hfq_campaign_id,\n"
                    + "    hfq_aid,\n"
                    + "    hfq_origin\n"
                    + "    FROM origin.hfq_marketing_account_id_mapping\n"
                    + ") b\n"
                    + "on upper(a.unique_channel) = upper(b.channel) and a.hfq_advertiser_id = b.hfq_advertiser_id\n"
                    + "and a.hfq_campaign_id = b.hfq_campaign_id and a.hfq_aid = b.hfq_aid\n"
                    + "where a.rn = 1;";

    public static final String clkSqlTmplateDesc =
            "select \n"
                    + "a.device_id,\n"
                    + "a.unique_channel,\n"
                    + "a.hfq_click_id,\n"
                    + "b.hfq_origin\n"
                    + "from \n"
                    + "(\n"
                    + "    select \n"
                    + "    device_id,\n"
                    + "    unique_channel,\n"
                    + "    hfq_advertiser_id,\n"
                    + "    hfq_campaign_id,\n"
                    + "    hfq_aid,\n"
                    + "    hfq_click_id,\n"
                    + "    row_number() over(partition by device_id, unique_channel order by dt_time desc) as rn\n"
                    + "    from\n"
                    + "    (\n"
                    + "        SELECT \n"
                    + "        device_id,\n"
                    + "        dt_time,\n"
                    + "        unique_channel,\n"
                    + "        rt_type,\n"
                    + "        max(if(label_name ='hfq_advertiser_id', label_value, null)) as hfq_advertiser_id,\n"
                    + "        max(if(label_name ='hfq_campaign_id', label_value, null)) as hfq_campaign_id,\n"
                    + "        max(if(label_name ='hfq_aid', label_value, null)) as hfq_aid,\n"
                    + "        max(if(label_name ='hfq_click_id', label_value, null)) as hfq_click_id\n"
                    + "        FROM clk_attribution.user_label_0214\n"
                    + "        where device_id = ?\n"
                    + "        and datediff(current_timestamp(), dt_time) <= ?\n"
                    + "        and rt_type = ?\n"
                    + "        group by device_id,dt_time, unique_channel, rt_type\n"
                    + "    ) tmp\n"
                    + ") a \n"
                    + "left join (\n"
                    + "    select \n"
                    + "    channel,\n"
                    + "    hfq_advertiser_id,\n"
                    + "    hfq_campaign_id,\n"
                    + "    hfq_aid,\n"
                    + "    hfq_origin\n"
                    + "    FROM origin.hfq_marketing_account_id_mapping\n"
                    + ") b\n"
                    + "on upper(a.unique_channel) = upper(b.channel) and a.hfq_advertiser_id = b.hfq_advertiser_id\n"
                    + "and a.hfq_campaign_id = b.hfq_campaign_id and a.hfq_aid = b.hfq_aid\n"
                    + "where a.rn = 1;";

    public static final String expSqlTmplateAsc =
            "select \n"
                    + "a.device_id,\n"
                    + "a.unique_channel,\n"
                    + "a.hfq_click_id,\n"
                    + "b.hfq_origin\n"
                    + "from \n"
                    + "(\n"
                    + "    select \n"
                    + "    device_id,\n"
                    + "    unique_channel,\n"
                    + "    hfq_advertiser_id,\n"
                    + "    hfq_campaign_id,\n"
                    + "    hfq_aid,\n"
                    + "    hfq_click_id,\n"
                    + "    row_number() over(partition by device_id, unique_channel order by dt_time asc) as rn\n"
                    + "    from\n"
                    + "    (\n"
                    + "        SELECT \n"
                    + "        device_id,\n"
                    + "        dt_time,\n"
                    + "        unique_channel,\n"
                    + "        rt_type,\n"
                    + "        max(if(label_name ='hfq_advertiser_id', label_value, null)) as hfq_advertiser_id,\n"
                    + "        max(if(label_name ='hfq_campaign_id', label_value, null)) as hfq_campaign_id,\n"
                    + "        max(if(label_name ='hfq_aid', label_value, null)) as hfq_aid,\n"
                    + "        max(if(label_name ='hfq_click_id', label_value, null)) as hfq_click_id\n"
                    + "        FROM exp_attribution.user_label_0214\n"
                    + "        where device_id = ?\n"
                    + "        and datediff(current_timestamp(), dt_time) <= ?\n"
                    + "        and rt_type = ?\n"
                    + "        group by device_id,dt_time, unique_channel, rt_type\n"
                    + "    ) tmp\n"
                    + ") a \n"
                    + "left join (\n"
                    + "    select \n"
                    + "    channel,\n"
                    + "    hfq_advertiser_id,\n"
                    + "    hfq_campaign_id,\n"
                    + "    hfq_aid,\n"
                    + "    hfq_origin\n"
                    + "    FROM origin.hfq_marketing_account_id_mapping\n"
                    + ") b\n"
                    + "on upper(a.unique_channel) = upper(b.channel) and a.hfq_advertiser_id = b.hfq_advertiser_id\n"
                    + "and a.hfq_campaign_id = b.hfq_campaign_id and a.hfq_aid = b.hfq_aid\n"
                    + "where a.rn = 1;";

    public static final String expSqlTmplateDesc =
            "select \n"
                    + "a.device_id,\n"
                    + "a.unique_channel,\n"
                    + "a.hfq_click_id,\n"
                    + "b.hfq_origin\n"
                    + "from \n"
                    + "(\n"
                    + "    select \n"
                    + "    device_id,\n"
                    + "    unique_channel,\n"
                    + "    hfq_advertiser_id,\n"
                    + "    hfq_campaign_id,\n"
                    + "    hfq_aid,\n"
                    + "    hfq_click_id,\n"
                    + "    row_number() over(partition by device_id, unique_channel order by dt_time desc) as rn\n"
                    + "    from\n"
                    + "    (\n"
                    + "        SELECT \n"
                    + "        device_id,\n"
                    + "        dt_time,\n"
                    + "        unique_channel,\n"
                    + "        rt_type,\n"
                    + "        max(if(label_name ='hfq_advertiser_id', label_value, null)) as hfq_advertiser_id,\n"
                    + "        max(if(label_name ='hfq_campaign_id', label_value, null)) as hfq_campaign_id,\n"
                    + "        max(if(label_name ='hfq_aid', label_value, null)) as hfq_aid,\n"
                    + "        max(if(label_name ='hfq_click_id', label_value, null)) as hfq_click_id\n"
                    + "        FROM exp_attribution.user_label_0214\n"
                    + "        where device_id = ?\n"
                    + "        and datediff(current_timestamp(), dt_time) <= ?\n"
                    + "        and rt_type = ?\n"
                    + "        group by device_id,dt_time, unique_channel, rt_type\n"
                    + "    ) tmp\n"
                    + ") a \n"
                    + "left join (\n"
                    + "    select \n"
                    + "    channel,\n"
                    + "    hfq_advertiser_id,\n"
                    + "    hfq_campaign_id,\n"
                    + "    hfq_aid,\n"
                    + "    hfq_origin\n"
                    + "    FROM origin.hfq_marketing_account_id_mapping\n"
                    + ") b\n"
                    + "on upper(a.unique_channel) = upper(b.channel) and a.hfq_advertiser_id = b.hfq_advertiser_id\n"
                    + "and a.hfq_campaign_id = b.hfq_campaign_id and a.hfq_aid = b.hfq_aid\n"
                    + "where a.rn = 1;";
}
